package com.phuoc.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.phuoc.entities.Establishment;

public class EstablishmentDAO {
	// Database fields
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;
	private String[] allCols = { MySQLiteHelper.ESTAB_ID,
			MySQLiteHelper.ESTAB_NAME, MySQLiteHelper.ESTAB_TYPEOFESTAB,
			MySQLiteHelper.ESTAB_TYPEOFFOOD, MySQLiteHelper.ESTAB_LOCATION,
			MySQLiteHelper.ESTAB_DATEREVIEW, MySQLiteHelper.ESTAB_TYPEOFMEAL,
			MySQLiteHelper.ESTAB_COST, MySQLiteHelper.ESTAB_RATESERVICE,
			MySQLiteHelper.ESTAB_RATEASMOSPHERE, MySQLiteHelper.ESTAB_RATEFOOD,
			MySQLiteHelper.ESTAB_RATEOVERALL, MySQLiteHelper.ESTAB_COMMENT };

	public EstablishmentDAO(Context context) {
		dbHelper = new MySQLiteHelper(context);
	}

	// Open connection
	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	// Close connection
	public void close() {
		dbHelper.close();
	}

	// Create establishment
	public boolean createEstablishment(Establishment estab) {
		// ContentValues to hold our establishment
		ContentValues values = new ContentValues();
		// Add a value to a set
		values.put(MySQLiteHelper.ESTAB_ID, estab.getId());
		values.put(MySQLiteHelper.ESTAB_NAME, estab.getName());
		values.put(MySQLiteHelper.ESTAB_TYPEOFESTAB,
				estab.getIdTypeOfEstablishment());
		values.put(MySQLiteHelper.ESTAB_TYPEOFFOOD, estab.getIdTypeOfFood());
		values.put(MySQLiteHelper.ESTAB_LOCATION, estab.getLocation());

		long result = database.insert(MySQLiteHelper.TABLE_ESTABLISMENT, null,
				values);
		if (result == -1)
			return false;
		else
			return true;
	}

	// Delete establishment
	public void deleteEstablishment(Establishment estab) {
		String id = estab.getId();
		database.delete(MySQLiteHelper.TABLE_ESTABLISMENT,
				MySQLiteHelper.ESTAB_ID + " = '" + id + "'", null);
	}

	// Delete establishment by Id
	public void deleteEstablishmentById(String id) {
		database.delete(MySQLiteHelper.TABLE_ESTABLISMENT,
				MySQLiteHelper.ESTAB_ID + " = '" + id + "'", null);
	}

	// Get all establishments
	public List<Establishment> getAllEstablishments(boolean aToz) {
		List<Establishment> establishments = new ArrayList<Establishment>();
		Cursor cursor = null;
		// Ascending
		if (aToz) {
			cursor = database.query(MySQLiteHelper.TABLE_ESTABLISMENT, allCols,
					null, null, null, null, MySQLiteHelper.ESTAB_NAME + " ASC",
					null);
		} else {
			// Descending
			cursor = database.query(MySQLiteHelper.TABLE_ESTABLISMENT, allCols,
					null, null, null, null,
					MySQLiteHelper.ESTAB_NAME + " DESC", null);
		}

		cursor.moveToFirst();

		// walk through all records
		while (!cursor.isAfterLast()) {
			Establishment estab = cursorToEstablishment(cursor);
			establishments.add(estab);
			cursor.moveToNext();
		}

		// make sure to close cursor
		cursor.close();

		return establishments;
	}

	// Search all establishments
	public List<Establishment> searchAllEstablishments(String query,
			boolean aToz) {
		List<Establishment> establishments = new ArrayList<Establishment>();
		Cursor cursor = null;
		//Cursor cursor = database.query(MySQLiteHelper.TABLE_ESTABLISMENT,
		//		allCols, MySQLiteHelper.ESTAB_NAME + " LIKE '%" + query + "%'",
		//		null, null, null, null);

		// Ascending
		if (aToz) {
			cursor = database.query(MySQLiteHelper.TABLE_ESTABLISMENT,
					allCols, MySQLiteHelper.ESTAB_NAME + " LIKE '%" + query + "%'",
					null, null, null, MySQLiteHelper.ESTAB_NAME + " ASC");
		} else {
			// Descending
			cursor = database.query(MySQLiteHelper.TABLE_ESTABLISMENT,
					allCols, MySQLiteHelper.ESTAB_NAME + " LIKE '%" + query + "%'",
					null, null, null, MySQLiteHelper.ESTAB_NAME + " DESC");
		}

		cursor.moveToFirst();

		// walk through all records
		while (!cursor.isAfterLast()) {
			Establishment estab = cursorToEstablishment(cursor);
			establishments.add(estab);
			cursor.moveToNext();
		}

		// make sure to close cursor
		cursor.close();

		return establishments;
	}

	// Get all establishments by type of establishment
		public List<Establishment> getAllEstablishmentsByEstablishment(
				long idTypeOfEstablishment, boolean aToz) {
			List<Establishment> establishments = new ArrayList<Establishment>();
			Cursor cursor = null;
			// Ascending
			if (aToz) {
				cursor = database.query(MySQLiteHelper.TABLE_ESTABLISMENT, allCols,
						MySQLiteHelper.ESTAB_TYPEOFESTAB + " = " + idTypeOfEstablishment
						, null, null, null, null);
			} else {
				// Descending
				cursor = database.query(MySQLiteHelper.TABLE_ESTABLISMENT, allCols,
						MySQLiteHelper.ESTAB_TYPEOFESTAB + "=" + idTypeOfEstablishment
						, null, null, null,
						MySQLiteHelper.ESTAB_NAME + " DESC", null);
			}

			cursor.moveToFirst();

			// walk through all records
			while (!cursor.isAfterLast()) {
				Establishment estab = cursorToEstablishment(cursor);
				establishments.add(estab);
				cursor.moveToNext();
			}

			// make sure to close cursor
			cursor.close();

			return establishments;
		}
		
	// Get all establishments by type of establishment
	public List<Establishment> getAllEstablishmentsByEstablishment(
			long idTypeOfEstablishment, boolean aToz, String query) {
		List<Establishment> establishments = new ArrayList<Establishment>();
		Cursor cursor = null;
		// Ascending
		if (aToz) {
			cursor = database.query(MySQLiteHelper.TABLE_ESTABLISMENT, allCols,
					MySQLiteHelper.ESTAB_TYPEOFESTAB + " = " + idTypeOfEstablishment
					+ " AND " + MySQLiteHelper.ESTAB_NAME + " LIKE '%" + query + "%'"
					, null, null, null, null);
		} else {
			// Descending
			cursor = database.query(MySQLiteHelper.TABLE_ESTABLISMENT, allCols,
					MySQLiteHelper.ESTAB_TYPEOFESTAB + "=" + idTypeOfEstablishment
					+ " AND " + MySQLiteHelper.ESTAB_NAME + " LIKE '%" + query + "%'"
					, null, null, null,
					MySQLiteHelper.ESTAB_NAME + " DESC", null);
		}

		cursor.moveToFirst();

		// walk through all records
		while (!cursor.isAfterLast()) {
			Establishment estab = cursorToEstablishment(cursor);
			establishments.add(estab);
			cursor.moveToNext();
		}

		// make sure to close cursor
		cursor.close();

		return establishments;
	}

	// Get establishment by id
	public Establishment getEstablishmentById(String id) {
		Cursor cs = database.query(MySQLiteHelper.TABLE_ESTABLISMENT, allCols,
				MySQLiteHelper.ESTAB_ID + " = '" + id + "'", null, null, null,
				null);
		cs.moveToFirst();
		Establishment estab = cursorToEstablishment(cs);
		cs.close();

		return estab;
	}

	// Update establishment
	public boolean updateEstablishment(Establishment estab) {
		ContentValues values = new ContentValues();
		// Add a value to a set
		values.put(MySQLiteHelper.ESTAB_ID, estab.getId());
		values.put(MySQLiteHelper.ESTAB_NAME, estab.getName());
		values.put(MySQLiteHelper.ESTAB_TYPEOFESTAB,
				estab.getIdTypeOfEstablishment());
		values.put(MySQLiteHelper.ESTAB_TYPEOFFOOD, estab.getIdTypeOfFood());
		values.put(MySQLiteHelper.ESTAB_LOCATION, estab.getLocation());
		values.put(MySQLiteHelper.ESTAB_DATEREVIEW, estab.getDateReview());
		values.put(MySQLiteHelper.ESTAB_RATESERVICE, estab.getRateService());
		values.put(MySQLiteHelper.ESTAB_RATEASMOSPHERE,
				estab.getRateAtmosphere());
		values.put(MySQLiteHelper.ESTAB_RATEFOOD, estab.getRateFood());
		values.put(MySQLiteHelper.ESTAB_RATEOVERALL, estab.getRateOverall());
		values.put(MySQLiteHelper.ESTAB_TYPEOFMEAL, estab.getTypeOfMeal());
		values.put(MySQLiteHelper.ESTAB_COST, estab.getCost());
		values.put(MySQLiteHelper.ESTAB_COMMENT, estab.getComment());

		int result = database.update(MySQLiteHelper.TABLE_ESTABLISMENT, values,
				MySQLiteHelper.ESTAB_ID + " = '" + estab.getId() + "'", null);

		if (result > 0)
			return true;
		else
			return false;
	}

	// Checking Id is exists or not ?
	public boolean checkIdExists(String id) {
		Cursor cs = database.query(MySQLiteHelper.TABLE_ESTABLISMENT, allCols,
				MySQLiteHelper.ESTAB_ID + " = '" + id + "'", null, null, null,
				null);

		if (cs.getCount() > 0) {
			cs.close();
			return true;
		} else {
			cs.close();
			return false;
		}
	}

	// Cursor to entities
	private Establishment cursorToEstablishment(Cursor cursor) {
		Establishment estab = new Establishment();
		estab.setId(cursor.getString(0));
		estab.setName(cursor.getString(1));
		estab.setIdTypeOfEstablishment(cursor.getLong(2));
		estab.setIdTypeOfFood(cursor.getLong(3));
		estab.setLocation(cursor.getString(4));
		estab.setDateReview(cursor.getString(5));
		estab.setTypeOfMeal(cursor.getString(6));
		estab.setCost(cursor.getFloat(7));
		estab.setRateService(cursor.getFloat(8));
		estab.setRateAtmosphere(cursor.getFloat(9));
		estab.setRateFood(cursor.getFloat(10));
		estab.setRateOverall(cursor.getFloat(11));
		estab.setComment(cursor.getString(12));

		return estab;
	}

}
